package hhq;

import java.sql.*;

import com.alibaba.fastjson.JSONArray;

import bean.Record;
import bean.User;

public class ConDB {

	public static String className = "com.mysql.cj.jdbc.Driver";

    // mysql -h localhost -P 3306 -u root -p
    // 设置正确的数据库地址  localhost
	public static String url = "jdbc:mysql://172.17.0.3:3306/liuyan?useUnicode=true&useSSL=false&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai";
    public static String username="root";
    public static String pwd = "hhq";

	public static Connection con = null;
	public static Statement stmt = null;
	public static ResultSet rs = null;

    // 构造函数
    public ConDB(){
		try {
			con = DriverManager.getConnection(url, username, pwd);
			return;
		} catch (Exception e) {
			System.out.println("连接失败");
			e.printStackTrace();
			return;
		}
    }
    // 构造函数
    @Override
    protected void finalize() throws Exception{
        if (con != null)
			con.close();

    }

	public static void main(String[] args) {
        ConDB db=new ConDB();
        System.out.println("opening connection...");
		boolean ok = db.open();
        System.out.println("ok="+ok);
        db.close();

        System.out.println("has user hhq = " + db.check_name("hhq"));
	}

	static {
		try {
			Class.forName(className);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}


	public boolean open() {
		try {
			stmt = con.createStatement();
			return true;
		} catch (Exception e) {
			System.out.println("连接失败");
			e.printStackTrace();
			return false;
		}
	}

	public void close() {
		try {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 增删改
	public boolean update(String sql) {

		if (!open())
			return false;
		try {
			int count = stmt.executeUpdate(sql);
			if (count > 0)
				return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		} finally {
			close();
		}
		return false;
	}

	public  boolean query(String sql) {
		if( ! open()) {
			System.out.println("cannot open connection");
			return false;
		}
		try {
			rs = stmt.executeQuery(sql);
            return true;
		} catch (Exception e) {
			e.printStackTrace();
            return false;
		}
	}


	// 查询数量 select count(*) from User;
    // >0 OK, -1 result error , -2 mysql connect error
	public long query_num(String sql) {
		if(!query(sql)){
            return -2;
        }
		try {
			while(rs.next()) {
				long i=rs.getLong(1);
				return i;
			}
		} catch (SQLException e) {
			e.printStackTrace();
            return -1;
		}
		finally {
			close();
		}
		return -1;
	}

	// 检查用户名是否存在
	public   boolean check_name(String name) {
		String sql = "select count(*) from User where name='" + name + "';";
		return (query_num(sql) == 1) ;
	}

	// 注册用户
	public   boolean insert_user(User u) {
		if (check_name(u.getName())) // 检查用户名是否存在
			return false;
		String sql = String.format("insert into User values('%s', '%s');", u.getName(), u.getPwd());
		return update(sql);
	}

	// change用户
	public   boolean change_user(User u) {
		String sql = String.format("update User set pwd='%s' where name='%s' ;",
				 u.getPwd(), u.getName());
		return update(sql);
	}
	//查用户账号密码
	public   boolean login_user(User u) {
		String sql = String.format("select count(*) from User where name='%s' and pwd='%s' ;",
				u.getName(),u.getPwd());
		return query_num(sql) == 1;
	}

	// 留言回复
	public   boolean insert_liuyan(Record r) {
		String sql="";
		if(r.getRecall().equals("-1"))
			sql = String.format("insert into record(name,tim,title,content)"
					+" values('%s','%s','%s','%s');",
					r.getName(),r.getTim(),r.getTitle(),r.getContent()
					);
		else
			sql = String.format("insert into record(name,tim,title,content,recall)"
					+" values('%s','%s','%s','%s','%s');",
					r.getName(),r.getTim(),r.getTitle(),r.getContent(),r.getRecall()
					);
		return update(sql);
	}
	// 删除留言
	public   boolean del_liuyan(Record r) {
		String sql=String.format("delete from record where id='%s' or recall='%s' ;",
					r.getId(), r.getId()
				);
		return update(sql);
	}

	// 查看所有留言
	public  JSONArray see_liuyan(){
		String sql=String.format("select * from record;");
        JSONArray arr=new JSONArray();
		if(!query(sql)){
            return arr;
        }

		try {
			while(rs.next()) {
				Record r=new Record(
						rs.getString("id"),
						rs.getString("name"),
						rs.getString("tim"),
						rs.getString("title"),
						rs.getString("content"),
						rs.getString("recall")
						);
				arr.add(r);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			close();
		}
		return arr;
	}


}
